package toko.service.impl;

import java.io.BufferedReader;
import java.io.FileReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import toko.domain.Buku;
import toko.domain.Penjualan;
import toko.domain.PenjualanDetail;
import toko.service.TokoService;

public class TokoServiceJDBC implements TokoService {
	private static final String sqlInsert 
	= "insert into buku (kode, judul, pengarang, penerbit, harga) " +
			"values (?,?,?,?,?)";

	private static final String sqlSelectById 
	= "select * from buku where id=? ";

	private static final String sqlSelectByKode 
	= "select * from buku where kode like ? ";

	private static final String sqlSelectByJudul 
	= "select * from buku where judul like ? ";
	
	private static final String sqlDeleteById 
	= "delete from buku where id = ? ";
	
	private static final String sqlLastInsertId 
	= "select last_insert_id() ";
	
	private static final String sqlInsertPenjualan 
	= "insert into penjualan (waktu_transaksi, kasir, pembayaran) values (?,?,?)";
	
	private static final String sqlInsertPenjualanDetail 
	= "insert into penjualan_detail (id_penjualan, id_buku, quantity) values (?,?,?)";
	

	
	private Connection connection;
	private PreparedStatement psInsert;
	private PreparedStatement psInsertPenjualan;
	private PreparedStatement psInsertPenjualanDetail;

	private PreparedStatement psSelectById;
	private PreparedStatement psSelectByKode;
	private PreparedStatement psSelectByJudul;
	private PreparedStatement psDeleteById;

	public TokoServiceJDBC(Connection connection) throws Exception {
		this.connection = connection;
		psInsert = this.connection.prepareStatement(sqlInsert);
		psInsertPenjualan = this.connection.prepareStatement(sqlInsertPenjualan);
		psInsertPenjualanDetail = this.connection.prepareStatement(sqlInsertPenjualanDetail);
		psSelectById = this.connection.prepareStatement(sqlSelectById);
		psSelectByKode = this.connection.prepareStatement(sqlSelectByKode);
		psSelectByJudul = this.connection.prepareStatement(sqlSelectByJudul);
		psDeleteById = this.connection.prepareStatement(sqlDeleteById);
	}

	@Override
	public void simpan(Buku buku) {
		try {
			psInsert.setString(1, buku.getKode());
			psInsert.setString(2, buku.getJudul());
			psInsert.setString(3, buku.getPengarang());
			psInsert.setString(4, buku.getPenerbit());
			psInsert.setBigDecimal(5, buku.getHarga());
			psInsert.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void hapus(Buku buku) {
		try {
			psDeleteById.setInt(1, buku.getId());
			psDeleteById.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public Buku cariId(Integer id) {
		try {
			psSelectById.setInt(1, id);
			ResultSet rs = psSelectById.executeQuery();
			
			// kalau next false, berarti tidak ada record yang ditemukan
			if(!rs.next()) return null;
			
			Buku b = konversiResultSetMenjadiBuku(rs);
			return b;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	private Buku konversiResultSetMenjadiBuku(ResultSet rs) throws Exception {
		Buku b = new Buku();
		b.setId(rs.getInt("id"));
		b.setKode(rs.getString("kode"));
		b.setJudul(rs.getString("judul"));
		b.setPengarang(rs.getString("pengarang"));
		b.setPenerbit(rs.getString("penerbit"));
		b.setHarga(rs.getBigDecimal("harga"));
		return b;
	}

	@Override
	public List<Buku> cariKode(String kode) {
		try {
			List<Buku> hasil = new ArrayList<Buku>();
			psSelectByKode.setString(1, "%"+kode+"%");
			ResultSet rs = psSelectByKode.executeQuery();
			while(rs.next()){
				Buku b = konversiResultSetMenjadiBuku(rs);
				hasil.add(b);
			}
			return hasil;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Buku> cariJudul(String judul) {
		try {
			List<Buku> hasil = new ArrayList<Buku>();
			psSelectByJudul.setString(1, "%"+judul+"%");
			ResultSet rs = psSelectByJudul.executeQuery();
			while(rs.next()){
				Buku b = konversiResultSetMenjadiBuku(rs);
				hasil.add(b);
			}
			return hasil;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void importCsv(String file, Boolean pakaiHeader) {
		try {
			connection.setAutoCommit(false);
			BufferedReader reader = new BufferedReader(new FileReader(file));
			String data;
			
			if(pakaiHeader){
				reader.readLine(); // skip header
			}
			
			while((data = reader.readLine())!=null){
				Buku b = konversiCsvJadiBuku(data);
				simpan(b);
			}
			reader.close();
			connection.commit();
		} catch (Exception e) {
			try {
				System.out.println("Roll back, terjadi error");
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private Buku konversiCsvJadiBuku(String data) {
		String[] bukuProperties = data.split(",");
		if(bukuProperties.length != 5) throw new IllegalArgumentException("Invalid data ["+data+"]");
		
		Buku b = new Buku();
		b.setKode(bukuProperties[0]);
		b.setJudul(bukuProperties[1]);
		b.setPengarang(bukuProperties[2]);
		b.setPenerbit(bukuProperties[3]);
		b.setHarga(new BigDecimal(bukuProperties[4]));
		return b;
	}

	@Override
	public void simpan(Penjualan p) {
		try {
			connection.setAutoCommit(false);
			psInsertPenjualan.setDate(1, new java.sql.Date(p.getWaktuTransaksi().getTime()));
			psInsertPenjualan.setString(2, p.getKasir());
			psInsertPenjualan.setBigDecimal(3, p.getPembayaran());
			psInsertPenjualan.executeUpdate();
			
			ResultSet rs = connection.createStatement().executeQuery(sqlLastInsertId);
			if(!rs.next()) throw new IllegalStateException("Autogenerated ID tidak bisa diambil");
			Integer id = rs.getInt(1);
			
			for (PenjualanDetail detail : p.getDaftarPenjualanDetail()) {
				psInsertPenjualanDetail.setInt(1, id);
				psInsertPenjualanDetail.setInt(2, detail.getBuku().getId());
				psInsertPenjualanDetail.setInt(3, detail.getQuantity());
				psInsertPenjualanDetail.executeUpdate();
			}
			
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
